﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;

namespace IHISCETeim.Web.Export.FXModel
{
    public partial class ReportGetRZZRBD : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get {
                return "融资租赁报单情况汇总表.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "Sheet1";
            }
        }

        protected int UserId
        {
            get
            {
                return GetRequestValue<int>("u",0);
            }
        }

        protected DataTable sumdt = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            string wheresql = "";
            if (UserId > 0)
            {
                string agentsqltemp = SAreaManagerAgentController.GetMyAgentSqlWhere();
                wheresql = string.Format(" AliasName in ({0})", agentsqltemp);
            }
            DataTableCollection dts = FXFinanceLeaseController.ReportGetRZZRBD(wheresql);
            DataTable exportDT = dts[0];

            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);  
            //FOR写入
            int rowindexBase = 3;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {
               

                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["id"].ToString());
                row1.CreateCell(1).SetCellValue(dr["经销商"].ToString());
                row1.CreateCell(2).SetCellValue(Convert.ToInt32( dr["未上报台数"]));
                row1.CreateCell(3).SetCellValue(Convert.ToDouble(dr["未上报合同金额"]));
                row1.CreateCell(4).SetCellValue(Convert.ToInt32(dr["IHISCE审核中总台数"]));
                row1.CreateCell(5).SetCellValue(Convert.ToDouble(dr["IHISCE审核中总融资额"]));
                row1.CreateCell(6).SetCellValue(Convert.ToInt32(dr["租赁公司审核中总台数"]));
                row1.CreateCell(7).SetCellValue(Convert.ToDouble(dr["租赁公司审核中总融资额"]));
                row1.CreateCell(8).SetCellValue(Convert.ToInt32(dr["审核通过总台数"]));
                row1.CreateCell(9).SetCellValue(Convert.ToDouble(dr["审核通过总融资额"]));
                row1.CreateCell(10).SetCellValue(Convert.ToInt32(dr["待放款总台数"]));
                row1.CreateCell(11).SetCellValue(Convert.ToDouble(dr["待放款总融资额"]));
                row1.CreateCell(12).SetCellValue(Convert.ToInt32(dr["总台数"]));
                row1.CreateCell(13).SetCellValue(Convert.ToDouble( dr["总融资额"]));

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //合计行
            HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            rowSum.CreateCell(1).SetCellValue("合计:");
            rowSum.CreateCell(2).SetCellFormula("sum(C" + (rowindexBase+1).ToString() + ":C" + (rowReal).ToString() + ")");
            rowSum.CreateCell(3).SetCellFormula("sum(D" + (rowindexBase + 1).ToString() + ":D" + (rowReal).ToString() + ")");
            rowSum.CreateCell(4).SetCellFormula("sum(E" + (rowindexBase + 1).ToString() + ":E" + (rowReal).ToString() + ")");
            rowSum.CreateCell(5).SetCellFormula("sum(F" + (rowindexBase + 1).ToString() + ":F" + (rowReal).ToString() + ")");
            rowSum.CreateCell(6).SetCellFormula("sum(G" + (rowindexBase + 1).ToString() + ":G" + (rowReal).ToString() + ")");
            rowSum.CreateCell(7).SetCellFormula("sum(H" + (rowindexBase + 1).ToString() + ":H" + (rowReal).ToString() + ")");
            rowSum.CreateCell(8).SetCellFormula("sum(I" + (rowindexBase + 1).ToString() + ":I" + (rowReal).ToString() + ")");
            rowSum.CreateCell(9).SetCellFormula("sum(J" + (rowindexBase + 1).ToString() + ":J" + (rowReal).ToString() + ")");
            rowSum.CreateCell(10).SetCellFormula("sum(K" + (rowindexBase + 1).ToString() + ":K" + (rowReal).ToString() + ")");
            rowSum.CreateCell(11).SetCellFormula("sum(L" + (rowindexBase + 1).ToString() + ":L" + (rowReal).ToString() + ")");
            rowSum.CreateCell(12).SetCellFormula("sum(M" + (rowindexBase + 1).ToString() + ":M" + (rowReal).ToString() + ")");
            rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            WriteToFile();
        }
    }
}